package com.tom;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.Driver;

public class MemberDAO {
        public static Connection conn = null;

        public static Connection getConnection() {
                if (conn == null) {
                        try {
                                DriverManager.registerDriver(new Driver());
                                conn = DriverManager.getConnection(
                                                "jdbc:mysql://j.snpy.org/j102u07?useUnicode=true&characterEncoding=UTF-8", "j102u07", "j102u07.989");
                        } catch (SQLException e) {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                        }
                }
                return conn;

        }

        public List<Member> getAll() {
                List<Member> members = new ArrayList<Member>();
                try {
                        Statement stmt = getConnection().createStatement();
                        ResultSet rs = stmt.executeQuery("select * from member");
                        while (rs.next()) {
                                String id = rs.getString("id");
                                String password1 = rs.getString("password1");
                                String password2 = rs.getString("password2");
                                String name_c = rs.getString("name_c");
                                String name_a = rs.getString("name_a");
                                String brith = rs.getString("brith");
                                String tel_area1 = rs.getString("tel_area1");
                                String telephone1 = rs.getString("telephone1");
                                String telephone1_p = rs.getString("telephone1_p");
                                String mobile1 = rs.getString("mobile1");
                                String tel_area2 = rs.getString("tel_area2");
                                String telephone2 = rs.getString("telephone2");
                                String telephone2_p = rs.getString("telephone2_p");
                                String mobile2 = rs.getString("mobile2");
                                String address1 = rs.getString("address1");
                                String address2 = rs.getString("address2");
                                String original = rs.getString("original");
                                String professional = rs.getString("professional");
                                String company_num = rs.getString("company_num");
                                String company_name = rs.getString("company_name");
                                String company_job = rs.getString("company_job");
                                String company_tel_area = rs.getString("company_tel_area");
                                String company_tel = rs.getString("company_tel");
                                String company_tel_p = rs.getString("company_tel_p");
                                String contact_name = rs.getString("contact_name");
                                String contact_keep = rs.getString("contact_keep");
                                String contact_area = rs.getString("contact_area");
                                String contact_tel = rs.getString("contact_tel");
                                String contact_tel_p = rs.getString("contact_tel_p");
                                String email = rs.getString("email");
                                Member member = new Member(id, password1, password2, name_c, name_a, brith, tel_area1, telephone1, telephone1_p, mobile1, tel_area2, telephone2, telephone2_p, mobile2, address1, address2, original, professional, company_num, company_name, company_job, company_tel_area, company_tel, company_tel_p, contact_name, contact_keep, contact_area, contact_tel, contact_tel_p, email);
                                members.add(member);
                        }
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                return members;
        }

        public int delete(String id) {
                String sql = "delete from member where id=?";
                int rowCount = 0;
                try {
                        PreparedStatement pstmt = getConnection().prepareStatement(sql);
                        pstmt.setString(1, id);
                        rowCount = pstmt.executeUpdate();
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                return rowCount;
        }
        
        public int add(Member m){
                String sql = "insert into member(id,password1,password2,name_c,name_a,brith,tel_area1,telephone1,telephone1_p,mobile1,tel_area2,telephone2,telephone2_p,mobile2,address1,address2,original,professional,company_num,company_name,company_job,company_tel_area,company_tel,company_tel_p,contact_name,contact_keep,contact_area,contact_tel,contact_tel_p,email) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                int rowCount = 0;
                try {
                        PreparedStatement pstmt = getConnection().prepareStatement(sql);
                        pstmt.setString(1, m.getId());
                        pstmt.setString(2, m.getPassword1());
                        pstmt.setString(3, m.getPassword2());
                        pstmt.setString(4, m.getName_c());
                        pstmt.setString(5, m.getName_a());
                        pstmt.setString(6, m.getBrith());
                        pstmt.setString(7, m.getTel_area1());
                        pstmt.setString(8, m.getTelephone1());
                        pstmt.setString(9, m.getTelephone1_p());
                        pstmt.setString(10, m.getMobile1());
                        pstmt.setString(11, m.getTel_area2());
                        pstmt.setString(12, m.getTelephone2());
                        pstmt.setString(13, m.getTelephone2_p());
                        pstmt.setString(14, m.getMobile2());
                        pstmt.setString(15, m.getAddress1());
                        pstmt.setString(16, m.getAddress2());
                        pstmt.setString(17, m.getOriginal());
                        pstmt.setString(18, m.getProfessional());
                        pstmt.setString(19, m.getCompany_num());
                        pstmt.setString(20, m.getCompany_name());
                        pstmt.setString(21, m.getCompany_job());
                        pstmt.setString(22, m.getCompany_tel_area());
                        pstmt.setString(23, m.getCompany_tel());
                        pstmt.setString(24, m.getCompany_tel_p());
                        pstmt.setString(25, m.getContact_name());
                        pstmt.setString(26, m.getContact_keep());
                        pstmt.setString(27, m.getContact_area());
                        pstmt.setString(28, m.getContact_tel());
                        pstmt.setString(29, m.getContact_tel_p());
                        pstmt.setString(30, m.getEmail());
                        rowCount = pstmt.executeUpdate();
                        System.out.println(rowCount);
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                return rowCount;
        }
        
        public int update(Member m){
                String sql = "update member set password1=?,password2=?,name_c=?,name_a=?,brith=?,tel_area1=?,telephone1=?,telephone1_p=?,mobile1=?,tel_area2=?,telephone2=?,telephone2_p=?,mobile2=?,address1=?,address2=?,original=?,professional=?,company_num=?,company_name=?,company_job=?,company_tel_area=?,company_tel=?,company_tel_p=?,contact_name=?,contact_keep=?,contact_area=?,contact_tel=?,contact_tel_p=?,email=?where id=?";
                int rowCount = 0;
                try {
                        PreparedStatement pstmt = getConnection().prepareStatement(sql);
                        pstmt.setString(1, m.getPassword1());
                        pstmt.setString(2, m.getPassword2());
                        pstmt.setString(3, m.getName_c());
                        pstmt.setString(4, m.getName_a());
                        pstmt.setString(5, m.getBrith());
                        pstmt.setString(6, m.getTel_area1());
                        pstmt.setString(7, m.getTelephone1());
                        pstmt.setString(8, m.getTelephone1_p());
                        pstmt.setString(9, m.getMobile1());
                        pstmt.setString(10, m.getTel_area2());
                        pstmt.setString(11, m.getTelephone2());
                        pstmt.setString(12, m.getTelephone2_p());
                        pstmt.setString(13, m.getMobile2());
                        pstmt.setString(14, m.getAddress1());
                        pstmt.setString(15, m.getAddress2());
                        pstmt.setString(16, m.getOriginal());
                        pstmt.setString(17, m.getProfessional());
                        pstmt.setString(18, m.getCompany_num());
                        pstmt.setString(19, m.getCompany_name());
                        pstmt.setString(20, m.getCompany_job());
                        pstmt.setString(21, m.getCompany_tel_area());
                        pstmt.setString(22, m.getCompany_tel());
                        pstmt.setString(23, m.getCompany_tel_p());
                        pstmt.setString(24, m.getContact_name());
                        pstmt.setString(25, m.getContact_keep());
                        pstmt.setString(26, m.getContact_area());
                        pstmt.setString(27, m.getContact_tel());
                        pstmt.setString(28, m.getContact_tel_p());
                        pstmt.setString(29, m.getEmail());
                        pstmt.setString(30, m.getId());
                        rowCount = pstmt.executeUpdate();
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                return rowCount;
        }

        public Member getMemberById(String id){
                Member member = null;
                String sql = "select * from member where id=?";
                int rowCount = 0;
                try {
                        PreparedStatement pstmt = getConnection().prepareStatement(sql);
                        pstmt.setString(1, id);
                        ResultSet rs = pstmt.executeQuery();
                        if (rs.next()){
                                member = new Member(rs.getString("id")
                                                , rs.getString("password1")
                                                , rs.getString("password2")
                                                , rs.getString("name_c")
                                                , rs.getString("name_a")
                                                , rs.getString("brith")
                                                , rs.getString("tel_area1")
                                                , rs.getString("telephone1")
                                                , rs.getString("telephone1_p")
                                                , rs.getString("mobile1")
                                                , rs.getString("tel_area2")
                                                , rs.getString("telephone2")
                                                , rs.getString("telephone2_p")
                                                , rs.getString("mobile2")
                                                , rs.getString("address1")
                                                , rs.getString("address2")
                                                , rs.getString("original")
                                                , rs.getString("professional")
                                                , rs.getString("company_num")
                                                , rs.getString("company_name")
                                                , rs.getString("company_job")
                                                , rs.getString("company_tel_area")
                                                , rs.getString("company_tel")
                                                , rs.getString("company_tel_p")
                                                , rs.getString("contact_name")
                                                , rs.getString("contact_keep")
                                                , rs.getString("contact_area")
                                                , rs.getString("contact_tel")
                                                , rs.getString("contact_tel_p")
                                                , rs.getString("email"));
                        }
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                return member;
        }
        
       /* public static void main(String[] args){
//              testgetAll();
                MemberDAO dao = new MemberDAO();
                System.out.println(dao.delete("aa123"));
        }

        private static void testgetAll() {
                MemberDAO dao = new MemberDAO();
                List<Member> list = dao.getAll();
                System.out.println(list.size());
        }
        
        */
        
}